# Copyright (c) 2025, qiushike and contributors
# For license information, please see license.txt

import copy
import frappe
from frappe import _, msgprint
from frappe.query_builder.functions import Count,Sum, Cast
from frappe.query_builder import Case


def execute(filters=None):
    if not filters:
        filters = {}

    columns = get_columns(filters)
    # 员工在表头（多个员工分组报工）
    data_employee_in_header = get_data(filters)
    # 员工在实际工时记录上
    data_employee_in_timelog = get_data(filters, employee_job_card_level = True)
    data = data_employee_in_header + data_employee_in_timelog      
    data = sorted(data, key=lambda r: (r['period'], r['workstation']))
    summary_level = filters.get('summary_level')
    data = summarize(summary_level, data)
    ## 最后将以述各种准备好的数据作为data变量返回data = columns, results, message, chart, report_summary
    return columns, data, '', None, None

def get_chart(data):
    """构造图表输出数据结构
        chart = {
        'data': {
            'labels': ["One", "Two", "Three"],
            'datasets': [
                {
                    'name': "Female", 'type': "bar",
                    'values': [3, 5, 7]
                },
            ]
        },
        'type': "line"
    }        
    """
    #1. 获取横坐标 [日期1,日期2..。】
    labels = distinct_sort([d.period for d in data])
    #2. 获取数据点字典 操作员1：{日期1：数量，}
    operator_dict = {}
    for d in data:
        operator_dict.setdefault(d.operator, {}).update({d.period: d.wo_qty})
    #3. 按横坐标画点, 二层遍历，Operator, date 
    datasets = []
    for operator, qty_dict in operator_dict.items():
        datasets.append({
            'name': operator,
            'values': [qty_dict.get(r, 0) for r in labels]
        })
    chart = {
        'data': {
            'labels': labels,
            'datasets': datasets
        },
        'type': "line"
    }
    return chart    

# 使用集合去重并排序
def distinct_sort(lis):
    sort_list = []
    for i in sorted(set(lis)):
        sort_list.append(i)
    return sort_list

def summarize(summary_level, data):
    if summary_level != 'Day':
        result = []
        row = frappe._dict()
        last_employee, last_period = None, None
        for d in data:
            employee = d.employee
            period = get_period(d.period, summary_level)
            if employee != last_employee or period != last_period:
                last_employee = employee
                last_period = period
                if row: result.append(row)
                row = copy.deepcopy(d)
                row.period = period
            elif row.qty and d.qty:
                row.qty += d.qty
                row.amount += d.amount                
        if row: result.append(row)
        data = result
    return data    

def get_columns(filters):
    columns = [
        {
            "label": _("Employee"),
            "options": "Employee",
            "fieldname": "employee",
            "fieldtype": "Link",
            "width": 140,
        },
        {
            "label": _("Date") if filters.get('summary_level') == "Day" else _("Period"),
            "fieldname": "period",
            "fieldtype": "Date" if filters.get('summary_level') == 'Day' else "Data",
            "width": 140
        },        
        {
            "label": _("Workstation"),
            "options": "Workstation",
            "fieldname": "workstation",
            "fieldtype": "Link",
            "width": 140,
        },
        {
            "label": _("Operation"),
            "options": "Operation",
            "fieldname": "operation",
            "fieldtype": "Link",
            "width": 140,
        },
        {
            "label": _("Qty"), 
            "fieldname": "qty", 
            "fieldtype": "Int", 
            "width": 140
        },
        {
            "label": _("Rate"),
            "fieldname": "service_price",
            "fieldtype": "Float",            
            "width": 80,
        },
        {
            "label": _("Amount"),            
            "fieldname": "amount",
            "fieldtype": "Float",            
            "width": 80,
        }
    ]

    return columns

def get_data(filters, employee_job_card_level = False):
    """
    employee_job_card_level: 员工信息输入在加工单表头
    另一种场景是输入在工时明细表行
    """
    jc = frappe.qb.DocType('Job Card').as_('jc')
    jstl = frappe.qb.DocType('Job Card Time Log').as_('jstl')
    jstl_job_card = frappe.qb.DocType('Job Card Time Log').as_('jstl_job_card')
    emp = frappe.qb.DocType('Employee').as_('emp')
    ws = frappe.qb.DocType('Workstation').as_('ws')
    query = frappe.qb.from_(jc
        ).select(jc.posting_date.as_('period'),
                emp.first_name.as_('employee'),
                jc.workstation,
                jc.operation,
                jc.service_price_base_qty,
                jc.custom_service_price.as_("service_price"),
                jstl.completed_qty.as_('qty')
        ).where(jc.docstatus == 1)
    if not filters.to_date:
        query = query.where(jc.posting_date == filters.from_date)
    else:
        query = query.where((jc.posting_date >= filters.from_date) & (jc.posting_date <= filters.to_date))
    if employee_job_card_level:
        query = query.join(jstl_job_card
            ).on((jstl_job_card.parent == jc.name)  & (jstl_job_card.parentfield == 'employee')
            ).join(emp
            ).on(jstl_job_card.employee == emp.name
            ).join(jstl
            ).on((jstl.parent == jc.name) & (jstl.parentfield == 'time_logs'))            
    else:
        query = query.join(jstl
            ).on((jstl.parent == jc.name) & (jstl.parentfield == 'time_logs')
            ).join(emp
            ).on(jstl.employee == emp.name)
    workstations = filters.workstations
    if workstations:
        query = query.where(jc.workstation.isin(workstations))
    if filters.operation:
        query = query.where(jc.operation==filters.operation)
    employees = [filters.employee] if filters.employee else get_allowed_employees()
    query = query.where(emp.name.isin(employees))
    
    data = query.run(as_dict = True)
    for d in data:
        if d.service_price_base_qty:
            d.qty = d.service_price_base_qty
        d.amount = d.qty * d.service_price
    return data or []

def get_period(in_date, summary_level):
    if summary_level == 'Day':
        res = in_date
    elif summary_level == 'Week':
        week_tuple = in_date.isocalendar()
        res = _('W {0}/{1}').format(week_tuple[0],week_tuple[1])
    elif summary_level == 'Month':
        res = _('M {0}/{1}').format(in_date.year, in_date.month)
    elif summary_level == 'Year':
        res = _('Y {0}').format(in_date.year)
    return res

@frappe.whitelist()
def get_allowed_employees():
    employees = frappe.db.get_list('Employee', pluck="name")
    user_employee_group = frappe.qb.DocType("User Employee Group")
    employee_group_table = frappe.qb.DocType("Employee Group Table")

    data = frappe.qb.from_(user_employee_group
    ).join(employee_group_table
    ).on(user_employee_group.employee_group == employee_group_table.parent
    ).where(
        user_employee_group.user == frappe.session.user
    ).select(
        employee_group_table.employee
    ).run(pluck="employee")

    employees.extend(data)
    employees=set(employees)
    return employees

@frappe.whitelist()
@frappe.validate_and_sanitize_search_inputs
def employee_query(doctype, txt, searchfield, start, page_len, filters):
    employees = get_allowed_employees()
    data = frappe.get_all("Employee", 
        filters={"name": ("in", employees)},
        or_filters = ({
                "name":("like", f"%{txt}%"),
                "first_name":("like", f"%{txt}%")    
            } if txt else {}
        ),
        fields=["name", "first_name"],
        as_list=1
    )

    return data